package com.warrior.superdata.handler;

import com.warrior.superdata.constants.CommonConstant;
import com.warrior.superdata.enums.SortType;
import com.warrior.superdata.mysql.entity.dto.ApiResultDto;
import com.warrior.superdata.mysql.entity.dto.ApiTableRelationDto;
import com.warrior.superdata.enums.ConditionType;
import com.warrior.superdata.enums.RelationType;
import com.warrior.superdata.mysql.entity.dto.ApiInfoDetailDto;
import com.warrior.superdata.mysql.entity.dto.ApiParamDto;
import com.warrior.superdata.util.CommonUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;

import java.util.List;
import java.util.stream.Collectors;

/**
 * @Description:
 * @ClassName: SqlHandler.java
 * @date: 2022/2/23 17:31
 * @author xujian
 */
@Component
@Slf4j
public class SqlHandler {

    public String generateSql(ApiInfoDetailDto apiInfoDetailDto) {
        StringBuffer from = generateFrom(apiInfoDetailDto);
        StringBuffer query = generateQuerySql(apiInfoDetailDto.getResults());
        StringBuffer whereCondition = generateWhereCondition(apiInfoDetailDto.getParams());
        StringBuffer limitSql = limitSql(apiInfoDetailDto);
        StringBuffer sql = query.append(from).append(whereCondition);
        generateSortSql(apiInfoDetailDto, sql);
        sql.append(limitSql);
        log.info("generateSql : {}", sql);
        return sql.toString();
    }

    public String generateCountSql(ApiInfoDetailDto apiInfoDetailDto) {
        StringBuffer query = new StringBuffer(" select count(1) ");
        StringBuffer from = generateFrom(apiInfoDetailDto);
        StringBuffer whereCondition = generateWhereCondition(apiInfoDetailDto.getParams());
        StringBuffer sql = query.append(from).append(whereCondition);
        log.info("generateCountSql : {}", sql);
        return sql.toString();
    }

    public StringBuffer generateFrom(ApiInfoDetailDto apiInfoDetailDto) {
        String fromTable = apiInfoDetailDto.getMainTableName();
        StringBuffer sb = new StringBuffer(" from ");
        //自定义sql已输入别名
        if (CommonConstant.API_CUSTOMIZESQL.equals(apiInfoDetailDto.getIsCustomizeSql())) {
            sb.append(fromTable).append(" ");
        } else {
            //配置接口别名默认表名
            sb.append(fromTable).append(" ").append(fromTable).append(" ");
        }
        List<ApiTableRelationDto> tableRelations = apiInfoDetailDto.getTableRelations();
        if (!CollectionUtils.isEmpty(tableRelations)) {
            tableRelations.forEach(relation -> {
                sb.append(RelationType.getNameByType(relation.getRelationType())).append(" ").
                        append(relation.getAssociationTableName()).append(" ").append(relation.getAssociationTableName()).
                        append(" on ").append(relation.getAssociationCondition());
            });
        }

        //自定义sql关联查询sql拼接
        if (CommonConstant.API_CUSTOMIZESQL.equals(apiInfoDetailDto.getIsCustomizeSql()) && null != apiInfoDetailDto.getApiCustomizeSqlDto()
                && StringUtils.isNotBlank(apiInfoDetailDto.getApiCustomizeSqlDto().getAssociationSql())) {
            sb.append(" ").append(apiInfoDetailDto.getApiCustomizeSqlDto().getAssociationSql()).append(" ");
        }

        return sb;
    }

    private void generateSortSql(ApiInfoDetailDto apiInfoDetailDto, StringBuffer sql) {
        List<ApiResultDto> collect = apiInfoDetailDto.getResults().stream().
                filter(item -> SortType.NONE.getType() != item.getSortType()).
                collect(Collectors.toList());
        if (!CollectionUtils.isEmpty(collect)) {
            StringBuffer sort = new StringBuffer(" ORDER BY ");
            for (int i = 0; i < collect.size(); i++) {
                if (SortType.DESC.getType() == collect.get(i).getSortType()) {
                    sort.append(collect.get(i).getResultTableFieldName()).append(" ").append(SortType.DESC.getName());
                } else if (SortType.ASC.getType() == collect.get(i).getSortType()) {
                    sort.append(collect.get(i).getResultTableFieldName()).append(" ").append(SortType.ASC.getName());
                }
                if (i != collect.size() - 1) {
                    sort.append(" , ");
                }
            }
            sql.append(sort);
        }
    }

    private StringBuffer generateWhereCondition(List<ApiParamDto> params) {
        StringBuffer sb = new StringBuffer(" where 1=1 and ");
        params.forEach(param -> {
            String conditionTypeName = ConditionType.getNameByType(param.getConditionType());
            if (ConditionType.LIKE.getType().equals(param.getConditionType())) {
                sb.append(param.getParamTableFieldName()).append(conditionTypeName).append("'%").
                        append(param.getParamValue()).append("%'").append(" and ");
            } else {
                if (CommonUtil.hasCastString(param.getParamJavaType())) {
                    sb.append(param.getParamTableFieldName()).append(conditionTypeName).
                            append("'").append(param.getParamValue()).append("'").append(" and ");
                } else {
                    sb.append(param.getParamTableFieldName()).append(conditionTypeName).
                            append(param.getParamValue()).append(" and ");
                }
            }
        });

        int lastAndIndex = sb.lastIndexOf("and");
        if (-1 != lastAndIndex) {
            sb.delete(lastAndIndex, lastAndIndex + 3);
        }
        return sb;
    }

    private StringBuffer generateQuerySql(List<ApiResultDto> results) {
        StringBuffer sb = new StringBuffer("select ");
        results.forEach(result -> {
            sb.append(result.getResultTableFieldName()).append(" ").
                    append(result.getResultName()).append(",");
        });
        if (-1 != sb.lastIndexOf(",")) {
            sb.deleteCharAt(sb.lastIndexOf(","));
        }
        return sb;
    }

    private StringBuffer limitSql(ApiInfoDetailDto apiInfoDetailDto) {
        StringBuffer sb = new StringBuffer();
        Integer resultType = apiInfoDetailDto.getResultType();
        if (0 == resultType) {
            sb.append(" limit 1 ");
        }
        //如果mysql数据库分页码可以直接limit
        if (CommonConstant.API_DATA_RESULT_PAGE == resultType
                && CommonConstant.DATASOURCE_MYSQL == apiInfoDetailDto.getDatasourceType()) {
            int page = apiInfoDetailDto.getPage();
            int size = apiInfoDetailDto.getSize();
            sb.append(" limit ").append(size * (page - 1)).append(",").append(size);
        }
        return sb;
    }

    public String generateInsertSql(ApiInfoDetailDto apiInfoDetailDto) {
        StringBuffer sb = new StringBuffer(" insert into ");
        String mainTableName = apiInfoDetailDto.getMainTableName();
        sb.append(mainTableName).append(" (");

        StringBuffer fileds = new StringBuffer();

        StringBuffer vaules = new StringBuffer(" values ");
        List<ApiParamDto> params = apiInfoDetailDto.getParams();

        String[] fieldValue = params.get(0).getParamValue().toString().split(CommonConstant.FIELD_VALUE_SPLICE_LIST);
        for (int i = 0; i < params.size(); i++) {
            if (i != params.size() - 1) {
                fileds.append(params.get(i).getParamTableFieldName().split("\\.")[1]).append(",");
            } else {
                fileds.append(params.get(i).getParamTableFieldName().split("\\.")[1]);
            }
        }

        for (int i = 0; i < fieldValue.length; i++) {
            for (int j = 0; j < params.size(); j++) {
                StringBuffer filedValues = new StringBuffer();
                String value = params.get(j).getParamValue().toString().split(CommonConstant.FIELD_VALUE_SPLICE_LIST)[i];
                value = (StringUtils.isBlank(value) || CommonConstant.NULL_STRING.equals(value)) ? null : "'" + value + "'";
                if (j == 0) {
                    filedValues.append("(");
                }

                if ((j == params.size() - 1) && i != fieldValue.length - 1) {
                    filedValues.append(value).append("),");
                } else if ((j == params.size() - 1) && i == fieldValue.length - 1) {
                    filedValues.append(value).append(")");
                } else {
                    filedValues.append(value).append(",");
                }

                vaules.append(filedValues);
            }
        }

        fileds.append(")");

        String insertSql = sb.append(fileds).append(vaules).toString();
        log.info("mysql inserSql : {}", insertSql);
        return insertSql;
    }

    public String generateUpdateSql(ApiInfoDetailDto apiInfoDetailDto) {
        StringBuffer sb = new StringBuffer(" update ");
        String mainTableName = apiInfoDetailDto.getMainTableName();
        sb.append(mainTableName);

        List<ApiParamDto> params = apiInfoDetailDto.getParams();
        List<ApiParamDto> updateParam = params.stream().filter(item -> 1 == item.getIsUpdate()).
                collect(Collectors.toList());

        StringBuffer setSb = new StringBuffer(" set ");
        for (int i = 0; i < updateParam.size(); i++) {
            if (CommonUtil.hasCastString(updateParam.get(i).getParamJavaType())) {
                setSb.append(updateParam.get(i).getParamTableFieldName().split("\\.")[1]).append("=").append("'").append(updateParam.get(i).getParamValue()).append("'");
            } else {
                setSb.append(updateParam.get(i).getParamTableFieldName().split("\\.")[1]).append("=").append(updateParam.get(i).getParamValue());
            }
            if (i != updateParam.size() - 1) {
                setSb.append(",");
            }
        }
        sb.append(setSb);

        List<ApiParamDto> conditionParam = params.stream().filter(item -> 0 == item.getIsUpdate()).collect(Collectors.toList());

        if (!CollectionUtils.isEmpty(conditionParam)) {
            StringBuffer condition = generateWhereCondition(conditionParam);
            sb.append(condition);

        }

        String result = sb.toString();
        log.info("mysql updateSql : {}", result);
        return result;
    }

    public String generateDeleteSql(ApiInfoDetailDto apiInfoDetailDto) {
        StringBuffer sb = new StringBuffer(" delete from  ");
        String mainTableName = apiInfoDetailDto.getMainTableName();
        sb.append(mainTableName);
        List<ApiParamDto> params = apiInfoDetailDto.getParams();
        if (!CollectionUtils.isEmpty(params)) {
            StringBuffer condition = generateWhereCondition(params);
            sb.append(condition);
        }

        String result = sb.toString();
        log.info("mysql deleteSql : {}", result);
        return result;
    }

}
